package com.sf.dlb.jpa.repository;

import com.sf.dlb.jpa.model.UserWalletInfo;
import com.sf.dlb.jpa.model.WalletLockInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 解锁信息表 Repository
 *
 * Created by jing.tan on 2018/5/21.
 */
@Repository
public interface WalletLockRepository extends JpaRepository<WalletLockInfo, Long> {
    /**
     * 根据用户ID-查询
     * @param userId
     * @param DelFlag
     * @return
     */
    List<WalletLockInfo> findByUserIdAndDelFlag(String userId, String DelFlag);
    /**
     * 根据用户ID+解锁状态 - 查询
     * @param userId
     * @param DelFlag
     * @return
     */
    List<WalletLockInfo> findByUserIdAndLockStateAndDelFlagOrderByCreateDateDesc(String userId, String lockState, String DelFlag);
    /**
     * 根据用户ID+解锁状态 - 查询解锁总数
     * @param userId
     * @param DelFlag
     * @return
     */
    @Query(value = "select sum(cast(a.lock_count as decimal(18,3))) from wallet_lock_info a where a.user_id=?1 and lock_state=?2 and a.del_flag=?3",nativeQuery = true)
    Double findByUserIdAndLockStateAndDelFlagCount(String userId, String lockState, String DelFlag);

    /**
     * 根据解锁流水号-查询
     * @param lockNumber
     * @param DelFlag
     * @return
     */
    WalletLockInfo findByLockNumberAndDelFlag(String lockNumber, String DelFlag);
    /**
     * 解锁中的数据-查询
     * @param lockState
     * @param DelFlag
     * @return
     */
    @Query(value = "select * from wallet_lock_info a where a.lock_state=?1 and a.lock_type=?2 and a.del_flag=?3 and to_days(now()) - to_days(a.create_date)>=?4",nativeQuery = true)
    List<WalletLockInfo> findByLockStateAndLockTypeAndDelFlag(String lockState, String lockType, String DelFlag, int day);

}